﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Osiptel.HECO.BE;
using System.Data.OracleClient;
using System.Data;

namespace Osiptel.HECO.DL
{
    public class ComponenteDL
    {
        public List<ComponenteBE> ObtenerComponentes(int tipo, int subTipo, int clase)
        {
            try
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.CommandText = "PKG_HECO_GLOBAL.SP_LISTAR_COMPONENTES";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("pIdTipo", OracleType.Number).Value = tipo;
                    cmd.Parameters.Add("pIdSubTipo", OracleType.Number).Value = subTipo;
                    cmd.Parameters.Add("pIdClase", OracleType.Number).Value = clase;
                    cmd.Parameters.Add("oListado", OracleType.Cursor).Direction = ParameterDirection.Output;
                    using (DL_CONEXION NewConexion = new DL_CONEXION())
                    {
                        try
                        {
                            NewConexion.retOpen();
                            cmd.Connection = NewConexion.retConexion();
                            ComponenteBE objComponenteBE;
                            List<ComponenteBE> listaComponente = new List<ComponenteBE>();
                            using (OracleDataReader objDRParametroDet = cmd.ExecuteReader())
                            {
                                while (objDRParametroDet.Read())
                                {
                                    objComponenteBE = new ComponenteBE();
                                    objComponenteBE.IdComponente = Convert.ToInt32(objDRParametroDet["IDCOMPONENTE"]);
                                    objComponenteBE.Descripcion = Convert.ToString(objDRParametroDet["DESCRIPCION"]);
                                    objComponenteBE.P01Unidad = Convert.ToInt32(objDRParametroDet["P01UNIDAD"]);
                                    objComponenteBE.P01UnidadDesc = Convert.ToString(objDRParametroDet["P01UNIDADDESC"]);
                                    objComponenteBE.P07Clase = Convert.ToInt32(objDRParametroDet["P07CLASE"]);
                                    objComponenteBE.P07ClaseDesc = Convert.ToString(objDRParametroDet["P07CLASEDESC"]);
                                    objComponenteBE.PrecioUnitarioMinimo = Convert.ToDecimal(objDRParametroDet["PRECIOMINIMO"]);
                                    objComponenteBE.PrecioUnitarioMaximo = Convert.ToDecimal(objDRParametroDet["PRECIOMAXIMO"]);
                                    objComponenteBE.Capacidad = Convert.ToString(objDRParametroDet["CAPACIDAD"]);
                                    objComponenteBE.InfoPublica = Convert.ToInt32(objDRParametroDet["INFOPUBLICA"]);
                                    //DateTime fechaActual = Convert.ToDateTime(objDRParametroDet["FECHAACTUAL"]);
                                    
                                    listaComponente.Add(objComponenteBE);
                                }
                                objDRParametroDet.Close();
                            }
                            NewConexion.retClose();
                            return listaComponente;
                        }
                        catch (Exception ex)
                        {
                            string error = ex.ToString();
                            List<ComponenteBE> listaComponente = new List<ComponenteBE>();
                            return listaComponente;
                        }
                        finally
                        {
                            if (NewConexion.conexion.State == ConnectionState.Open) NewConexion.retClose();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public int mInsertar(ComponenteBE componente)
        {

            try
            {
                Int32 id = 0;

                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.CommandText = "PKG_HECO_GLOBAL.SP_INSERTAR_COMPONENTE";
                    cmd.CommandType = CommandType.StoredProcedure;

                    OracleParameter prm1 = new OracleParameter("PNOMBRE", OracleType.VarChar);
                    OracleParameter prm2 = new OracleParameter("PP01UNIDAD", OracleType.Int32);
                    OracleParameter prm3 = new OracleParameter("PDESCRIPCION", OracleType.VarChar);
                    OracleParameter prm4 = new OracleParameter("PNOMBRECORTO", OracleType.VarChar);
                    OracleParameter prm5 = new OracleParameter("PCODIGO", OracleType.Char);
                    OracleParameter prm6 = new OracleParameter("PP07CLASE", OracleType.Int32);
                    OracleParameter prm7 = new OracleParameter("PTIEMPOVIDAANIO", OracleType.Int32);
                    OracleParameter prm8 = new OracleParameter("PUSUCRE", OracleType.VarChar);
                    OracleParameter prm9 = new OracleParameter("PIPREG", OracleType.VarChar);
                    OracleParameter prm10 = new OracleParameter("PCAPACIDAD", OracleType.VarChar);
                    OracleParameter prm11 = new OracleParameter("PINFOPUBLICA", OracleType.Char);
                    OracleParameter prm12 = new OracleParameter("PIDCOMP", OracleType.Int32);


                    prm1.Direction = ParameterDirection.Input;
                    prm2.Direction = ParameterDirection.Input;
                    prm3.Direction = ParameterDirection.Input;
                    prm4.Direction = ParameterDirection.Input;
                    prm5.Direction = ParameterDirection.Input;
                    prm6.Direction = ParameterDirection.Input;
                    prm7.Direction = ParameterDirection.Input;
                    prm8.Direction = ParameterDirection.Input;
                    prm9.Direction = ParameterDirection.Input;
                    prm10.Direction = ParameterDirection.Input;
                    prm11.Direction = ParameterDirection.Input;
                    prm12.Direction = ParameterDirection.Output;

                    prm1.Value = componente.Nombre;
                    prm2.Value = componente.P01Unidad;
                    prm3.Value = componente.Descripcion;
                    prm4.Value = componente.NombreCorto;
                    prm5.Value = componente.Codigo;
                    prm6.Value = componente.P07Clase;
                    prm7.Value = componente.TiempoVidaAnio;
                    prm8.Value = componente.UsuCre;
                    prm9.Value = componente.IpReg;
                    prm10.Value = componente.Capacidad;
                    prm11.Value = componente.InfoPublica;
                    prm12.Value = componente.IdComponente;

                    cmd.Parameters.Add(prm1);
                    cmd.Parameters.Add(prm2);
                    cmd.Parameters.Add(prm3);
                    cmd.Parameters.Add(prm4);
                    cmd.Parameters.Add(prm5);
                    cmd.Parameters.Add(prm6);
                    cmd.Parameters.Add(prm7);
                    cmd.Parameters.Add(prm8);
                    cmd.Parameters.Add(prm9);
                    cmd.Parameters.Add(prm10);
                    cmd.Parameters.Add(prm11);
                    cmd.Parameters.Add(prm12);

                    using (DL_CONEXION NewConexion = new DL_CONEXION())
                    {
                        int ret = NewConexion.ejecutaSQL(cmd);
                        id = Convert.ToInt32(Convert.ToString(cmd.Parameters["PIDCOMP"].Value));
                        return id;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void mInsertarTipo(ComponenteTipoBE componente)
        {

            try
            {
                Int32 id = 0;

                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.CommandText = "PKG_HECO_GLOBAL.SP_INSERTAR_COMPONENTETIPO";
                    cmd.CommandType = CommandType.StoredProcedure;

                    OracleParameter prm1 = new OracleParameter("PP05TIPO", OracleType.Int32);
                    OracleParameter prm2 = new OracleParameter("PUSUCRE", OracleType.VarChar);
                    OracleParameter prm3 = new OracleParameter("PIPREG", OracleType.VarChar);
                    OracleParameter prm4 = new OracleParameter("PIDCOMP", OracleType.Int32);

                    prm1.Direction = ParameterDirection.Input;
                    prm2.Direction = ParameterDirection.Input;
                    prm3.Direction = ParameterDirection.Input;
                    prm4.Direction = ParameterDirection.Input;

                    prm1.Value = componente.P05Tipo;
                    prm2.Value = componente.UsuCre;
                    prm3.Value = componente.IpReg;
                    prm4.Value = componente.IdComponente;

                    cmd.Parameters.Add(prm1);
                    cmd.Parameters.Add(prm2);
                    cmd.Parameters.Add(prm3);
                    cmd.Parameters.Add(prm4);

                    using (DL_CONEXION NewConexion = new DL_CONEXION())
                    {
                        NewConexion.ejecutaSQL(cmd);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void mInsertarSubTipo(ComponenteSubtipoBE componente)
        {

            try
            {
                Int32 id = 0;

                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.CommandText = "PKG_HECO_GLOBAL.SP_INSERTAR_COMPONENTESUBTIPO";
                    cmd.CommandType = CommandType.StoredProcedure;

                    OracleParameter prm1 = new OracleParameter("PP06SUBTIPO", OracleType.Int32);
                    OracleParameter prm2 = new OracleParameter("PUSUCRE", OracleType.VarChar);
                    OracleParameter prm3 = new OracleParameter("PIPREG", OracleType.VarChar);
                    OracleParameter prm4 = new OracleParameter("PIDCOMP", OracleType.Int32);

                    prm1.Direction = ParameterDirection.Input;
                    prm2.Direction = ParameterDirection.Input;
                    prm3.Direction = ParameterDirection.Input;
                    prm4.Direction = ParameterDirection.Input;

                    prm1.Value = componente.P06SubTipo;
                    prm2.Value = componente.UsuCre;
                    prm3.Value = componente.IpReg;
                    prm4.Value = componente.IdComponente;

                    cmd.Parameters.Add(prm1);
                    cmd.Parameters.Add(prm2);
                    cmd.Parameters.Add(prm3);
                    cmd.Parameters.Add(prm4);

                    using (DL_CONEXION NewConexion = new DL_CONEXION())
                    {
                        NewConexion.ejecutaSQL(cmd);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public ComponenteBE Obtener(int pIDComponente)
        {
            try
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.CommandText = "PKG_HECO_GLOBAL.SP_OBTENER_COMPONENTE";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    OracleParameter prm1 = new OracleParameter("pIDComponente", OracleType.Int32);
                    OracleParameter prm2 = new OracleParameter("oListado", OracleType.Cursor);
                    prm1.Direction = ParameterDirection.Input;
                    prm2.Direction = ParameterDirection.Output;
                    prm1.Value = pIDComponente;
                    cmd.Parameters.Add(prm1);
                    cmd.Parameters.Add(prm2);

                    using (DL_CONEXION NewDL_CONEXION = new DL_CONEXION())
                    {
                        try
                        {
                            NewDL_CONEXION.retOpen();
                            cmd.Connection = NewDL_CONEXION.retConexion();
                            ComponenteBE objConsultaBE = new ComponenteBE();
                            using (OracleDataReader oRead = cmd.ExecuteReader())
                            {
                                if (oRead.Read())
                                {
                                    objConsultaBE.IdComponente = Convert.ToInt32(oRead.GetValue(oRead.GetOrdinal("IDCOMPONENTE")));
                                    objConsultaBE.Nombre = Convert.ToString(oRead.GetValue(oRead.GetOrdinal("NOMBRE")));
                                    objConsultaBE.Descripcion = Convert.ToString(oRead.GetValue(oRead.GetOrdinal("DESCRIPCION")));
                                    objConsultaBE.P07Clase = Convert.ToInt32(oRead.GetValue(oRead.GetOrdinal("P07CLASE")));
                                    objConsultaBE.P07ClaseDesc = Convert.ToString(oRead.GetValue(oRead.GetOrdinal("P07CLASEDESC")));
                                    objConsultaBE.PrecioUnitarioMinimo = Convert.ToDecimal(oRead.GetValue(oRead.GetOrdinal("PRECIOMINIMO")));
                                    objConsultaBE.PrecioUnitarioMaximo = Convert.ToDecimal(oRead.GetValue(oRead.GetOrdinal("PRECIOMAXIMO")));
                                }
                                oRead.Close();
                            }
                            NewDL_CONEXION.retClose();
                            return objConsultaBE;
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }

                        finally
                        {
                            if (NewDL_CONEXION.conexion.State == ConnectionState.Open) NewDL_CONEXION.retClose();

                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
         
        public List<ComponenteBE> ObtenerCompRadioEnlace()
        {
            using (DL_CONEXION NewConexion = new DL_CONEXION())
            {
                try
                {
                    NewConexion.retOpen();
                    OracleCommand cmd = NewConexion.retConexion().CreateCommand();
                    cmd.CommandText = "PKG_HECO_GLOBAL.SP_OBTENER_COMP_RADIOENLACE";
                    cmd.CommandType = CommandType.StoredProcedure;
                    OracleParameter prm3 = new OracleParameter("oListado", OracleType.Cursor);
                    prm3.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(prm3);

                    ComponenteBE objConsultaBE;
                    List<ComponenteBE> lstConsultaBE = new List<ComponenteBE>();
                    using (OracleDataReader objDRConsulta = cmd.ExecuteReader())
                    {
                        while (objDRConsulta.Read())
                        {
                            objConsultaBE = new ComponenteBE();
                            objConsultaBE.IdComponente = Convert.ToInt32(objDRConsulta["IDCOMPONENTE"]);
                            objConsultaBE.Nombre = Convert.ToString(objDRConsulta["NOMBRE"]);
                            objConsultaBE.Descripcion = Convert.ToString(objDRConsulta["DESCRIPCION"]);
                            objConsultaBE.P07Clase = Convert.ToInt32(objDRConsulta["P07CLASE"]);
                            objConsultaBE.P07ClaseDesc = Convert.ToString(objDRConsulta["P07CLASEDESC"]);
                            objConsultaBE.PrecioUnitarioMinimo = Convert.ToDecimal(objDRConsulta["PRECIOMINIMO"]);
                            objConsultaBE.PrecioUnitarioMaximo = Convert.ToDecimal(objDRConsulta["PRECIOMAXIMO"]);
                            lstConsultaBE.Add(objConsultaBE);
                        }
                        objDRConsulta.Close();
                    }
                    NewConexion.retClose();
                    return lstConsultaBE;
                }
                catch (Exception ex)
                {
                    string error = ex.ToString();
                    List<ComponenteBE> lstConsultaBE = new List<ComponenteBE>();
                    return lstConsultaBE;
                }
                finally
                {
                    if (NewConexion.conexion.State == ConnectionState.Open) NewConexion.retClose();
                }
            }
        }

        public List<ComponenteBE> FListarComponentes(int pagina, int regxpagina)
        {
            using (DL_CONEXION NewConexion = new DL_CONEXION())
            {
                try
                {
                    NewConexion.retOpen();
                    OracleCommand cmd = NewConexion.retConexion().CreateCommand();
                    cmd.CommandText = "PKG_HECO_GLOBAL.SP_LISTAR_COMPONENTE_PAGINADO";
                    cmd.CommandType = CommandType.StoredProcedure;

                    OracleParameter prm1 = new OracleParameter("pagina", OracleType.Int32);
                    OracleParameter prm2 = new OracleParameter("tamanio", OracleType.Int32);
                    OracleParameter prm3 = new OracleParameter("oListado", OracleType.Cursor);
                    prm1.Direction = ParameterDirection.Input;
                    prm2.Direction = ParameterDirection.Input;
                    prm3.Direction = ParameterDirection.Output;
                    prm1.Value = pagina;
                    prm2.Value = regxpagina;
                    cmd.Parameters.Add(prm1);
                    cmd.Parameters.Add(prm2);
                    cmd.Parameters.Add(prm3);

                    ComponenteBE objConsultaBE;
                    List<ComponenteBE> lstConsultaBE = new List<ComponenteBE>();
                    using (OracleDataReader objDRConsulta = cmd.ExecuteReader())
                    {
                        while (objDRConsulta.Read())
                        {
                            objConsultaBE = new ComponenteBE();
                            objConsultaBE.IdComponente = Convert.ToInt32(objDRConsulta["idcomponente"]);
                            objConsultaBE.Nombre = Convert.ToString(objDRConsulta["nombre"]);
                            lstConsultaBE.Add(objConsultaBE);
                        }
                        objDRConsulta.Close();
                    }
                    NewConexion.retClose();
                    return lstConsultaBE;
                }
                catch (Exception ex)
                {
                    string error = ex.ToString();
                    List<ComponenteBE> lstConsultaBE = new List<ComponenteBE>();
                    return lstConsultaBE;
                }
                finally
                {
                    if (NewConexion.conexion.State == ConnectionState.Open) NewConexion.retClose();
                }
            }
        }


        public List<ComponenteBE> ObtenerTodosComponentes()
        {
            try
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.CommandText = "PKG_HECO_GLOBAL.SP_LISTAR_TODOS_COMPONENTES";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("oListado", OracleType.Cursor).Direction = ParameterDirection.Output;
                    using (DL_CONEXION NewConexion = new DL_CONEXION())
                    {
                        try
                        {
                            NewConexion.retOpen();
                            cmd.Connection = NewConexion.retConexion();
                            ComponenteBE objComponenteBE;
                            List<ComponenteBE> listaComponente = new List<ComponenteBE>();
                            using (OracleDataReader objDRParametroDet = cmd.ExecuteReader())
                            {
                                while (objDRParametroDet.Read())
                                {
                                    objComponenteBE = new ComponenteBE();
                                    objComponenteBE.IdComponente = Convert.ToInt32(objDRParametroDet["IDCOMPONENTE"]);
                                    objComponenteBE.Descripcion = Convert.ToString(objDRParametroDet["DESCRIPCION"]);
                                    objComponenteBE.P01Unidad = Convert.ToInt32(objDRParametroDet["P01UNIDAD"]);
                                    objComponenteBE.P01UnidadDesc = Convert.ToString(objDRParametroDet["P01UNIDADDESC"]);
                                    objComponenteBE.P07Clase = Convert.ToInt32(objDRParametroDet["P07CLASE"]);
                                    objComponenteBE.P07ClaseDesc = Convert.ToString(objDRParametroDet["P07CLASEDESC"]);
                                    objComponenteBE.TiempoVidaAnio = Convert.ToInt32(objDRParametroDet["TIEMPOVIDAANIO"]);
                                    objComponenteBE.Capacidad = Convert.ToString(objDRParametroDet["CAPACIDAD"]);
                                    objComponenteBE.InfoPublica = Convert.ToInt32(objDRParametroDet["INFOPUBLICA"]);
                                    objComponenteBE.PrecioUnitarioMinimo = Convert.ToDecimal(objDRParametroDet["PRECIOMINIMO"]);
                                    objComponenteBE.PrecioUnitarioMaximo = Convert.ToDecimal(objDRParametroDet["PRECIOMAXIMO"]);
                                    objComponenteBE.FecCre = Convert.ToDateTime(objDRParametroDet["FECCRE"]);
                                    listaComponente.Add(objComponenteBE);
                                }
                                objDRParametroDet.Close();
                            }
                            NewConexion.retClose();
                            return listaComponente;
                        }
                        catch (Exception ex)
                        {
                            string error = ex.ToString();
                            List<ComponenteBE> listaComponente = new List<ComponenteBE>();
                            return listaComponente;
                        }
                        finally
                        {
                            if (NewConexion.conexion.State == ConnectionState.Open) NewConexion.retClose();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }





}
